CREATE OR REPLACE PACKAGE ModifyAttrs IS

--===============================================================
-- Package information
--===============================================================
FUNCTION Version RETURN VARCHAR2
;
--===============================================================
-- Type declarations
--===============================================================
TYPE TYPE_ModifyAttrs_Input IS RECORD
(
    ContainerName Container.ContainerName%TYPE
    , AttributeNames TRGUtil.COLLECTION
    , NewValues TRGUtil.COLLECTION
    , NewRevisions TRGUtil.COLLECTION
    , OldValues TRGUtil.COLLECTION
    , OldRevisions TRGUtil.COLLECTION
    , CallByCDOName VARCHAR2(40)
)
;
--===============================================================
-- Function and Procedure declarations
--===============================================================
FUNCTION VerifyData (ContainerName IN VARCHAR2
                     , Delimiter IN VARCHAR2
                     , AttributeNames IN VARCHAR2
                     , NewValues IN VARCHAR2
                     , NewRevisions IN VARCHAR2
                     , OldValues IN VARCHAR2
                     , OldRevisions IN VARCHAR2
                     , CallByCDOName IN VARCHAR2)
RETURN VARCHAR2
;
PROCEDURE VerifyData2 (ContainerName IN VARCHAR2
                       , Delimiter IN VARCHAR2
                       , AttributeNames IN VARCHAR2
                       , NewValues IN VARCHAR2
                       , NewRevisions IN VARCHAR2
                       , OldValues IN VARCHAR2
                       , OldRevisions IN VARCHAR2
                       , CallByCDOName IN VARCHAR2)
;
FUNCTION ExecuteData (ContainerName IN VARCHAR2)
RETURN VARCHAR2
;
PROCEDURE ExecuteData2 (ContainerName IN VARCHAR2)
;
PROCEDURE GetNewValue (InputData IN TYPE_ModifyAttrs_Input
                       , AttributeName IN VARCHAR2
                       , NewValue OUT VARCHAR2)
;
PROCEDURE VerifyDateCode (InputData IN OUT TYPE_ModifyAttrs_Input
                          , NewValue IN VARCHAR2)
;
PROCEDURE VerifyFabDateCode (InputData IN OUT TYPE_ModifyAttrs_Input
                             , NewValue IN VARCHAR2)
;
--===========================================================================
-- End of package specification
--===========================================================================
END;
/
CREATE OR REPLACE PACKAGE BODY ModifyAttrs AS

--=====================
-- Package information
--=====================
FUNCTION Version RETURN VARCHAR2 IS BEGIN RETURN ('3.4.0001'); END;

--=====================
-- VerifyData
--=====================
FUNCTION VerifyData (ContainerName IN VARCHAR2
                     , Delimiter IN VARCHAR2
                     , AttributeNames IN VARCHAR2
                     , NewValues IN VARCHAR2
                     , NewRevisions IN VARCHAR2
                     , OldValues IN VARCHAR2
                     , OldRevisions IN VARCHAR2
                     , CallByCDOName IN VARCHAR2)
RETURN VARCHAR2 IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Local variables
    --=====================

    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Call the procedure
    --=====================
    VerifyData2 (ContainerName
                 , Delimiter
                 , AttributeNames
                 , NewValues
                 , NewRevisions
                 , OldValues
                 , OldRevisions
                 , CallByCDOName);
    --=====================
    -- Return success
    --=====================
    RETURN ('0');
EXCEPTION
    WHEN eExit THEN
        RETURN ('ModifyAttrs.VerifyData->Exited prematurely');
    WHEN eAppError THEN
        RETURN (REPLACE(SQLERRM, 'ORA-20005: '));
    WHEN OTHERS THEN
        RETURN ('ModifyAttrs.VerifyData->' || SQLERRM);
END VerifyData;

--=====================
-- VerifyData2
--=====================
PROCEDURE VerifyData2 (ContainerName IN VARCHAR2
                       , Delimiter IN VARCHAR2
                       , AttributeNames IN VARCHAR2
                       , NewValues IN VARCHAR2
                       , NewRevisions IN VARCHAR2
                       , OldValues IN VARCHAR2
                       , OldRevisions IN VARCHAR2
                       , CallByCDOName IN VARCHAR2)
IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Local variables
    --=====================
    tInputData TYPE_ModifyAttrs_Input;
    nCount NUMBER;
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Save the input variables
    --=====================
    tInputData.ContainerName := ContainerName;
    tInputData.AttributeNames := TRGUtil.SetCollection(AttributeNames, Delimiter);
    tInputData.NewValues := TRGUtil.SetCollection(NewValues, Delimiter);
    tInputData.NewRevisions := TRGUtil.SetCollection(NewRevisions, Delimiter);
    tInputData.OldValues := TRGUtil.SetCollection(OldValues, Delimiter);
    tInputData.OldRevisions := TRGUtil.SetCollection(OldRevisions, Delimiter);
    tInputData.CallByCDOName := CallByCDOName;
    --=====================
    -- Loop through each attribute to verify
    --=====================
    --FOR nCount IN 0..tInputData.AttributeNames.COUNT-1 LOOP
        --IF tInputData.AttributeNames(nCount) = 'DateCode' THEN
        --    VerifyDateCode (tInputData, tInputData.NewValues(nCount));
        --ELSIF tInputData.AttributeNames(nCount) = 'FabDateCode' THEN
        --    VerifyFabDateCode (tInputData, tInputData.NewValues(nCount));
        --END IF;
    --END LOOP;
EXCEPTION
    WHEN eAppError THEN
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR (-20005, 'ModifyAttrs.VerifyData2->' || SQLERRM);
END VerifyData2;

--=====================
-- ExecuteData
--=====================
FUNCTION ExecuteData (ContainerName IN VARCHAR2)
RETURN VARCHAR2 IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Local variables
    --=====================

    --=====================
    -- Pragma Declarations
    --=====================
    PRAGMA AUTONOMOUS_TRANSACTION;
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Call the procedure
    --=====================
    ExecuteData2 (ContainerName);
    --=====================
    -- Return success
    --=====================
    RETURN ('0');
EXCEPTION
    WHEN eExit THEN
        ROLLBACK;
        RETURN ('ModifyAttrs.ExecuteData->Exited prematurely');
    WHEN eAppError THEN
        ROLLBACK;
        RETURN (REPLACE(SQLERRM, 'ORA-20005: '));
    WHEN OTHERS THEN
        ROLLBACK;
        RETURN ('ModifyAttrs.ExecuteData->' || SQLERRM);
END ExecuteData;

--=====================
-- ExecuteData2
--=====================
PROCEDURE ExecuteData2 (ContainerName IN VARCHAR2)
IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Local variables
    --=====================

    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    --
    --=====================

    --=====================
    -- Commit the data
    --=====================
    COMMIT;
EXCEPTION
    WHEN eExit THEN
        ROLLBACK;
    WHEN eAppError THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'ModifyAttrs.ExecuteData2->' || SQLERRM);
END ExecuteData2;

--=====================
-- GetNewValue
--=====================
PROCEDURE GetNewValue (InputData IN TYPE_ModifyAttrs_Input
                       , AttributeName IN VARCHAR2
                       , NewValue OUT VARCHAR2)
IS
    --=====================
    -- Cursor Declarations
    --=====================

    --=====================
    -- Local variables
    --=====================
    nCount NUMBER;
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Find the attribute
    --=====================
    FOR nCount IN 0..InputData.AttributeNames.COUNT-1 LOOP
        IF InputData.AttributeNames(nCount) = AttributeName THEN
            NewValue := InputData.NewValues(nCount);
            EXIT;
        END IF;
    END LOOP;
EXCEPTION
    WHEN eAppError THEN
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR (-20005, 'ModifyAttrs.GetNewValue->' || SQLERRM);
END GetNewValue;

--=====================
-- VerifyDateCode
--=====================
PROCEDURE VerifyDateCode (InputData IN OUT TYPE_ModifyAttrs_Input
                          , NewValue IN VARCHAR2)
IS
    --=====================
    -- Cursor Declarations
    --=====================

    --=====================
    -- Local variables
    --=====================

    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    IF NewValue IS NOT NULL THEN
        --=====================
        -- Verify the format
        --=====================
        IF LENGTH(NewValue) < 4 OR LENGTH(NewValue) > 8 THEN
            RAISE_APPLICATION_ERROR (-20005, 'ModifyAttrs_E1010');
        END IF;
        IF SUBSTR(NewValue, 1, 1) < '0' OR SUBSTR(NewValue, 1, 1) > '9' OR
           SUBSTR(NewValue, 2, 1) < '0' OR SUBSTR(NewValue, 2, 1) > '9' THEN
            RAISE_APPLICATION_ERROR (-20005, 'ModifyAttrs_E1011');
        END IF;
        IF SUBSTR(NewValue, 3, 1) < '0' OR SUBSTR(NewValue, 3, 1) > '9' OR
           SUBSTR(NewValue, 4, 1) < '0' OR SUBSTR(NewValue, 4, 1) > '9' THEN
            RAISE_APPLICATION_ERROR (-20005, 'ModifyAttrs_E1012');
        ELSE
            IF TO_NUMBER(SUBSTR(NewValue, 3, 2)) < 1 OR TO_NUMBER(SUBSTR(NewValue, 3, 2)) > 53 THEN
                RAISE_APPLICATION_ERROR (-20005, 'ModifyAttrs_E1013');
            END IF;
        END IF;
    END IF;
EXCEPTION
    WHEN eAppError THEN
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR (-20005, 'ModifyAttrs.VerifyDateCode->' || SQLERRM);
END VerifyDateCode;

--=====================
-- VerifyFabDateCode
--=====================
PROCEDURE VerifyFabDateCode (InputData IN OUT TYPE_ModifyAttrs_Input
                             , NewValue IN VARCHAR2)
IS
    --=====================
    -- Cursor Declarations
    --=====================

    --=====================
    -- Local variables
    --=====================
    nCount NUMBER;
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    IF NewValue IS NOT NULL THEN
        --=====================
        -- Verify the format
        --=====================
        IF LENGTH(NewValue) <> 2 THEN
            RAISE_APPLICATION_ERROR (-20005, 'ModifyAttrs_E1030');
        END IF;
        IF SUBSTR(NewValue, 1, 1) < '0' OR SUBSTR(NewValue, 1, 1) > '9' THEN
            RAISE_APPLICATION_ERROR (-20005, 'ModifyAttrs_E1031');
        END IF;
        SELECT
            COUNT(*)
        INTO
            nCount
        FROM
            Container A
            , CurrentStatus B
            , Spec C
        WHERE
            A.CurrentStatusId = B.CurrentStatusId
            AND B.SpecId = C.SpecId
            AND A.ContainerName = InputData.ContainerName
            AND C.ObjectType = 'WAFERSORT'
        ;
        IF nCount > 0 THEN
            IF INSTR('ABCD', SUBSTR(NewValue, 2, 1)) = 0  THEN
                RAISE_APPLICATION_ERROR (-20005, 'ModifyAttrs_E1032');
            END IF;
        ELSE
            IF NewValue <> 'A'  THEN
                RAISE_APPLICATION_ERROR (-20005, 'ModifyAttrs_E1033');
            END IF;
        END IF;
    END IF;
EXCEPTION
    WHEN eAppError THEN
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR (-20005, 'ModifyAttrs.VerifyFabDateCode->' || SQLERRM);
END VerifyFabDateCode;

--===========================================================================
-- End of package body
--===========================================================================
END;
/
